﻿import unittest, datetime
from PDataBase import *


class Test_PDataBaseTest(unittest.TestCase):
    """
    Тестирование класса PDataBase.
    """
    def setUp(self):
        # Имена БД.
        self._names = ['testdb1', 'testdb2', 'testdb3']
        # Объекты тестируемых оберток БД.
        self._testDb = []

    def tearDown(self):
        self._clearTestDb()

    def _createTestDb(self):
        # Тестируемые БД.
        self._testDb = []

        # Создание первой тестовой БД.
        connection = sqlite.connect(self._names[0])
        cursor = connection.cursor()
        cursor.executescript("""
                            CREATE TABLE IF NOT EXISTS table11(id INTEGER PRIMARY KEY, field111 TEXT, field112 DATE);
                            CREATE TABLE IF NOT EXISTS table12(id INTEGER PRIMARY KEY, field121 TEXT, field122 DATE);
                            CREATE TABLE IF NOT EXISTS table13(id INTEGER PRIMARY KEY, field131 TEXT, field132 DATE, field133 INTEGER);
                            CREATE TABLE IF NOT EXISTS table14(id INTEGER PRIMARY KEY, field141 TEXT, field142 DATE);
                           """)
        connection.commit()
        connection.close()
        self._testDb.append(PDataBase(self._names[0]))

        # Создание второй тестовой БД.
        connection = sqlite.connect(self._names[1])
        cursor = connection.cursor()
        cursor.executescript("""
                            CREATE TABLE IF NOT EXISTS table21 (id INTEGER PRIMARY KEY, field211 TEXT, field212 DATE);
                            CREATE TABLE IF NOT EXISTS table22 (id INTEGER PRIMARY KEY, field221 TEXT, field222 DATE, field223 INTEGER, field224 BLOB, field225 REAL, field226 TEXT);
                            CREATE TABLE IF NOT EXISTS table23 (id INTEGER PRIMARY KEY, field231 TEXT, field232 DATE);
                           """)
        connection.commit()
        self._testDb.append(PDataBase(self._names[1]))

        # Создание третий тестовой БД.
        connection = sqlite.connect(self._names[2])
        cursor = connection.cursor()
        cursor.execute('CREATE TABLE IF NOT EXISTS table31 (id INTEGER PRIMARY KEY, field311 TEXT)')
        connection.commit()
        self._testDb.append(PDataBase(self._names[2]))

    def _clearTestDb(self):
        for db in self._testDb:
            cursor = db._connection.cursor()
            cursor.executescript(
                """ PRAGMA writable_schema = 1;
                                     delete from sqlite_master where type = 'table';
                                     PRAGMA writable_schema = 0;
                                     VACUUM;
                                     PRAGMA INTEGRITY_CHECK;
                """
            )
            db._connection.commit()

    def test_Ctor(self):
        # Создание тестируемых Бд.
        self._createTestDb()

        ethalons = [
                    [
                        ['table11', [(0, 'id', 'INTEGER'), (1, 'field111', 'TEXT'), (2, 'field112', 'DATE')]],
                        ['table12', [(0, 'id', 'INTEGER'), (1, 'field121', 'TEXT'), (2, 'field122', 'DATE')]],
                        ['table13', [(0, 'id', 'INTEGER'), (1, 'field131', 'TEXT'), (2, 'field132', 'DATE'), (3, 'field133', 'INTEGER')]],
                        ['table14', [(0, 'id', 'INTEGER'), (1, 'field141', 'TEXT'), (2, 'field142', 'DATE')]]
                    ],
                    [
                        ['table21', [(0, 'id', 'INTEGER'), (1, 'field211', 'TEXT'), (2, 'field212', 'DATE')]],
                        ['table22', [(0, 'id', 'INTEGER'), (1, 'field221', 'TEXT'), (2, 'field222', 'DATE'), (3, 'field223', 'INTEGER'), (4, 'field224', 'BLOB'), (5, 'field225', 'REAL'), (6, 'field226', 'TEXT')]],
                        ['table23', [(0, 'id', 'INTEGER'), (1, 'field231', 'TEXT'), (2, 'field232', 'DATE')]]
                    ],
                    [
                        ['table31', [(0, 'id', 'INTEGER'), (1, 'field311', 'TEXT')]]
                    ]
                   ]

        results = []
        for db in self._testDb:
            tmp = []
            for name in db.tablesNames():
                tmp.append([name, db.getTable(name).columns])
            results.append(tmp)

        for pair in zip(ethalons, results):
            self.assertEqual(pair[0], pair[1], 'Failed with {}.'.format(pair))

    def test_tablesNames(self):
        # Создание тестируемых Бд.
        self._createTestDb()

        ethalons = [
                    ['table11', 'table12', 'table13', 'table14'],
                    ['table21', 'table22', 'table23'],
                    ['table31'],
                   ]

        results = [db.tablesNames() for db in self._testDb]

        for pair in zip(ethalons, results):
            self.assertEqual(pair[0], pair[1], 'Failed with {}.'.format(pair))

    def test_getTable(self):
        # Создание тестируемых Бд.
        self._createTestDb()

        ethalons = [
                    ['table13', 'id', 'field131', 'field132', 'field133'],
                    ['table22', 'id', 'field221', 'field222', 'field223', 'field224', 'field225', 'field226'],
                    ['table31', 'id', 'field311']
                   ]

        results = [
                     [db.getTable(name).name,] + [col[1] for col in db.getTable(name).columns]
                     for (name, db) in zip(['table13', 'table22', 'table31'], self._testDb)
                  ]

        for pair in zip(ethalons, results):
            self.assertEqual(pair[0], pair[1], 'Failed with {}.'.format(pair))

    def test_deleteTable(self):
        # Создание тестируемых Бд.
        self._createTestDb()

        ethalons = [
                    ['table12', 'table13', 'table14'],
                    ['table21', 'table23'],
                    [],
                   ]

        for (name, db) in zip(['table11', 'table22', 'table31'], self._testDb):
            db.deleteTable(name)

        results = [db.tablesNames() for db in self._testDb]

        for pair in zip(ethalons, results):
            self.assertEqual(pair[0], pair[1], 'Failed with {}.'.format(pair))

    def test_createTable(self):
        # Создание тестируемых Бд.
        self._createTestDb()

        ethalons = [
                    ['new1', [(0, 'id', 'INTEGER'), (1, 'nfield11', 'REAL'), (2, 'nfield12', 'TEXT')], ['id', 'nfield11'], []],
                    ['new2', [(0, 'id', 'INTEGER'), (1, 'nfield21', 'BLOB'), (2, 'idnfield22', 'INTEGER'), (3, 'idnfield23', 'INTEGER')], ['id'], [('idnfield22', 'table21', 'id'), ('idnfield23', 'table22', 'id')]],
                    ['new3', [(0, 'id', 'INTEGER'), (1, 'nfield31', 'DATE'), (2, 'idnfield32', 'INTEGER')], ['id'], [('idnfield32', 'table31', 'id')]]
                   ]

        names = ['new1', 'new2', 'new3']
        columns = [
                    [(0, 'id', 'INTEGER'), (1, 'nfield11', 'REAL'), (2, 'nfield12', 'TEXT')],
                    [(0, 'id', 'INTEGER'), (1, 'nfield21', 'BLOB'), (2, 'idnfield22', 'INTEGER'), (3, 'idnfield23', 'INTEGER') ],
                    [(0, 'id', 'INTEGER'), (1, 'nfield31', 'DATE'), (2, 'idnfield32', 'INTEGER')]
                  ]
        primaryKeys = [
            ['id', 'nfield11'],
            ['id'],
            ['id']
        ]
        foreignKeys = [
            [],
            [('idnfield23', 'table22', 'id'), ('idnfield22', 'table21', 'id')],
            [('idnfield32', 'table31', 'id')]
        ]
        for (name, col, pk, fk, db) in zip(names, columns, primaryKeys, foreignKeys, self._testDb):
            db.createTable(name, col, pk, fk)

        results = [
                    [db.getTable(name).name, db.getTable(name).columns, db.getTable(name).primaryKeys, db.getTable(name).foreignKeys]
                    for (name, db) in zip(names, self._testDb)
                  ]

        for pair in zip(ethalons, results):
            self.assertEqual(pair[0], pair[1], 'Failed with {}.'.format(pair))

    def test_selectByQuery(self):
        # Создание тестируемых Бд.
        self._createTestDb()

        db = self._testDb[0]
        testTable = db.createTable(
            'test_find',
            [(0, 'id', 'INTEGER'), (1, 'a', 'INTEGER'), (2, 'b', 'REAL'), (3, 'c', 'TEXT'), (4, 'd', 'BLOB'), (5, 'e', 'DATE'), (6, 'f', 'TIMESTAMP')],
            ['id'],
            []
        )
        testTable.clear()
        testTable.addRow({'a': 15, 'b': 0.7, 'c': 'Test1', 'd': b'bytes1', 'e': datetime.date(2013, 4, 19), 'f': datetime.datetime.now()})
        testTable.addRow({'a': 150, 'b': 1.17, 'c': 'Test2', 'd': b'bytes2', 'e': datetime.date(2013, 7, 19), 'f': datetime.datetime(2014, 7, 19, 15, 29, 43, 79060)})
        testTable.addRow({'a': 1500, 'b': 2.7, 'c': 'Test3', 'd': b'bytes3', 'e': datetime.date(2013, 8, 19), 'f': datetime.datetime.now()})
        testTable.addRow({'a': 0, 'b': 12.7, 'c': 'Test4', 'd': b'bytes4', 'e': datetime.date(2013, 9, 1), 'f': datetime.datetime(2013, 9, 1, 8, 30, 0, 0)})
        testTable.addRow({'a': 10, 'b': 10.0, 'c': 'Test5', 'd': b'bytes5', 'e': datetime.date(2013, 9, 1), 'f': datetime.datetime(2013, 9, 1, 8, 30, 0, 0)})

        ethalons = [
            testTable.getAllRows(),
            [(1500,)],
            [(1,  'Test1')]
        ]
        selects = [
            '',
            'max (a)',
            'id, c'
        ]
        fromTables = [
            'test_find',
            'test_find',
            'test_find'
        ]
        wheres = [
            '',
            '',
            'b = (SELECT MIN(b) FROM test_find)'
        ]
        results = []
        for (f, s, w) in zip(fromTables, selects, wheres):
            results.append(db.selectByQuery(f, s, w))

        for pair in zip(ethalons, results):
            self.assertEqual(pair[0], pair[1], 'Failed with {}.'.format(pair))

if __name__ == '__main__':
    unittest.main()
